﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

using NHibernate;
using NHibernate.Cfg;
using NHibernate.Expression;

namespace DataAccess
{
    public class ManagerHelper
    {
        private static ISessionFactory sessionFactory;

        private static Configuration cfg;

        public static ISessionFactory InitSessionFactory()
        {
            if (sessionFactory == null)
            {
                cfg = new Configuration();
                cfg.Configure();
                cfg.AddXmlFile("FilterField.hbm.xml").
                    AddXmlFile("Filter.hbm.xml").
                    AddXmlFile("Role.hbm.xml").
                    AddXmlFile("User.hbm.xml").
                    AddXmlFile("EducationModule.hbm.xml").
                    AddXmlFile("EducationGroup.hbm.xml").
                    AddXmlFile("Document.hbm.xml").
                    AddXmlFile("Education.hbm.xml").
                    AddXmlFile("EducationEvent.hbm.xml").
                    AddXmlFile("UserEvent.hbm.xml"); ;
                return (sessionFactory = cfg.BuildSessionFactory());
            }
            return sessionFactory;
        }

        public static String getConnectionString()
        {
            if (cfg != null)
            {
                return cfg.Properties["hibernate.connection.connection_string"].ToString();
            }
            return null;
        }
    }

    public abstract class Manager<T>
        where T : ServiceObject
    {

        private const string ALIAS_PREFIX = "alias";

        private ISessionFactory factory;

        public T SaveOrUpdate(T obj)
        {
            ISession session = null;
            try
            {
                session = Factory.OpenSession();
                session.SaveOrUpdate(obj);
                session.Flush();
                return obj;
            }
            finally
            {
                if (session != null)
                {
                    session.Close();
                }
            }
        }

        public T GetById(long id)
        {
            ISession session = null;
            try
            {
                session = Factory.OpenSession();
                return session.Load(typeof(T), id) as T;
            }
            finally
            {
                if (session != null)
                {
                    session.Close();
                }
            }
        }

        public IList GetAll()
        {
            ISession session = null;
            try
            {
                session = Factory.OpenSession();
                return session.CreateCriteria(typeof(T)).List();
            }
            finally
            {
                if (session != null)
                {
                    session.Close();
                }
            }
        }

        public int GetListSize(Filter filter)
        {
            return GetAll().Count;
        }

        public IList GetList(Filter filter, int first, int size)
        {
            ISession session = null;
            try
            {
                session = Factory.OpenSession();
                ICriteria criteria = session.CreateCriteria(typeof(T));
                foreach (FilterField field in filter.Fields)
                {
                    if (!string.IsNullOrEmpty(field.Value))
                    {
                        String value = field.Value;
                        value = value.Replace("*", "%");
                        value.Replace("?", "_");
                        if (filter.TableName.Equals(field.TableName))
                        {
                            criteria.Add(Expression.Like(field.ColumnName, field.Value));
                        }
                        else
                        {
                            criteria.CreateAlias(field.TableName, ALIAS_PREFIX + field.Id);
                            criteria.Add(Expression.Like(ALIAS_PREFIX + field.Id +
                                                         "." + field.ColumnName, field.Value));
                        }
                    }
                }
                if (first >= 0 && size > 0)
                {
                    criteria.SetFirstResult(first).
                        SetMaxResults(size);
                }
                return criteria.List();
            }
            finally
            {
                if (session != null)
                {
                    session.Close();
                }
            }
        }

        public virtual bool Delete(T obj)
        {
            ISession session = null;
            try
            {
                session = Factory.OpenSession();
                session.Delete(obj);
                session.Flush();
                return true;
            }
            finally
            {
                if (session != null)
                {
                    session.Close();
                }
            }
        }

        public ISessionFactory Factory
        {
            get { return factory ?? (factory = ManagerHelper.InitSessionFactory()); }
            set { factory = value; }
        }
    }

    public class ManagerFilter : Manager<Filter>
    {

        private readonly Dictionary<String, String> storedProcedures;

        public ManagerFilter()
        {
            storedProcedures = new Dictionary<string, string>
                                   {
                                       {"User", "createUserFilter"},
                                       {"EducationModule", "createModuleFilter"},
                                       {"EducationGroup", "createGroupFilter"},
                                       {"Document", "createDocFilter"},
                                       {"Education", "createEducationFilter"}
                                   };
        }

        public Filter GetFilterByName(String name)
        {
            ISession session = null;
            try
            {
                session = Factory.OpenSession();
                return (Filter)session.CreateCriteria(typeof(Filter)).
                                   Add(Expression.Eq("Name", name)).
                                   UniqueResult();
            }
            catch
            {
                return null;
            }
            finally
            {
                if (session != null)
                {
                    session.Close();
                }
            }
        }

        public void CreateFilterByName(String name, String type)
        {
            SqlConnection conn = null;
            try
            {
                conn = new
                    SqlConnection(ManagerHelper.getConnectionString());
                conn.Open();
                var cmd = new SqlCommand(storedProcedures[type], conn) {CommandType = CommandType.StoredProcedure};
                cmd.Parameters.Add(new SqlParameter("@FilterName", name));
                cmd.ExecuteNonQuery();
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
            }
        }
    }

    public class ManagerUser : Manager<User>
    {
        public User Auth(String login, String password)
        {
            ISession session = null;
            try
            {
                session = Factory.OpenSession();
                return (User)session.CreateCriteria(typeof(User)).
                                 Add(Expression.Eq("Login", login)).
                                 Add(Expression.Eq("Password", password)).
                                 UniqueResult();
            }
            catch
            {
                return null;
            }
            finally
            {
                if (session != null)
                {
                    session.Close();
                }
            }
        }

        public User GetUserByEmail(String email)
        {
            ISession session = null;
            try
            {
                session = Factory.OpenSession();
                return (User)session.CreateCriteria(typeof(User)).
                                 Add(Expression.Eq("Email", email)).
                                 UniqueResult();
            }
            catch
            {
                return null;
            }
            finally
            {
                if (session != null)
                {
                    session.Close();
                }
            }
        }

        public User GetUserByLogin(String login)
        {
            ISession session = null;
            try
            {
                session = Factory.OpenSession();
                return (User)session.CreateCriteria(typeof(User)).
                                 Add(Expression.Eq("Login", login)).
                                 UniqueResult();
            }
            catch
            {
                return null;
            }
            finally
            {
                if (session != null)
                {
                    session.Close();
                }
            }
        }

        public override bool Delete(User user)
        {
            SqlConnection conn = null;
            try
            {
                conn = new
                    SqlConnection(ManagerHelper.getConnectionString());
                conn.Open();
                var cmd = new SqlCommand("deleteUser", conn) {CommandType = CommandType.StoredProcedure};
                cmd.Parameters.Add(new SqlParameter("@UserId", user.Id));
                cmd.ExecuteNonQuery();
                return true;
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
            }
        }
        
    }

    public class ManagerRole : Manager<Role>
    {
    }

    public class ManagerModule : Manager<EducationModule>
    {
        public EducationModule GetModuleByName(String name)
        {
            ISession session = null;
            try
            {
                session = Factory.OpenSession();
                return (EducationModule)session.CreateCriteria(typeof(EducationModule)).
                                            Add(Expression.Eq("Name", name)).
                                            UniqueResult();
            }
            catch
            {
                return null;
            }
            finally
            {
                if (session != null)
                {
                    session.Close();
                }
            }
        }
    }

    public class ManagerGroup : Manager<EducationGroup>
    {
    }

    public class ManagerDoc : Manager<Document>
    {
    }

    public class ManagerEducation : Manager<Education>
    {
        public override bool Delete(Education education)
        {
            SqlConnection conn = null;
            try
            {
                conn = new
                    SqlConnection(ManagerHelper.getConnectionString());
                conn.Open();
                var cmd = new SqlCommand("deleteEducation", conn) {CommandType = CommandType.StoredProcedure};
                if (cmd.Parameters != null)
                    cmd.Parameters.Add(new SqlParameter("@EducationId", education.Id));
                cmd.ExecuteNonQuery();
                return true;
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
            }
        }
    }
}